USE HierarchyIdDemo
GO

-- original schema for department hierarchy
CREATE TABLE [SelfJoin].Org (
	DepartmentID INT PRIMARY KEY CLUSTERED,
	DepartmentName VARCHAR(100) NOT NULL,
	ParentID INT NULL
	-- etc.
)
GO

-- add INDEX ON parent
CREATE INDEX SelfJoin_Org_ParentID ON SelfJoin.Org( ParentID )
GO

-- add the top level department
INSERT SelfJoin.Org VALUES (1, 'Entire Organization', NULL)

-- INSERT additional departments
INSERT SelfJoin.Org VALUES (2, 'Operations', 1)
INSERT SelfJoin.Org VALUES (3, 'Development', 1)
INSERT SelfJoin.Org VALUES (4, 'Parking', 1)
INSERT SelfJoin.Org VALUES (5, 'Home Operations', 2)
INSERT SelfJoin.Org VALUES (6, 'Field Operations', 2)
INSERT SelfJoin.Org VALUES (7, 'North Territory', 6)
INSERT SelfJoin.Org VALUES (8, 'South Territory', 6)
INSERT SelfJoin.Org VALUES (9,  'Database', 3)
INSERT SelfJoin.Org VALUES (10, 'Services', 3)
INSERT SelfJoin.Org VALUES (11, 'Applications', 3)
INSERT SelfJoin.Org VALUES (12,  'Windows', 11)
INSERT SelfJoin.Org VALUES (13, 'Internet', 11)
INSERT SelfJoin.Org VALUES (14, 'Self', 4)
INSERT SelfJoin.Org VALUES (15, 'Valet', 4)

SELECT * FROM SelfJoin.Org
GO